package com.aizuda.boot.modules.business.counterparty.service.impl;

import cn.hutool.core.thread.ThreadUtil;
import com.aizuda.boot.modules.business.contract.dto.SingleResponse;
import com.aizuda.boot.modules.business.contract.dto.UserInfo;
import com.aizuda.boot.modules.business.contract.entity.ContractFieldValueEntity;
import com.aizuda.boot.modules.business.contract.mapper.ContractFieldValueMapper;
import com.aizuda.boot.modules.business.counterparty.entity.CounterpartyTenantRel;
import com.aizuda.boot.modules.business.counterparty.mapper.CounterpartyTenantRelMapper;
import com.aizuda.boot.modules.common.constant.enums.ResponseCodeEnum;
import com.aizuda.boot.modules.business.contract.util.AuthUtil;
import com.aizuda.boot.modules.business.counterparty.entity.BankInfoEntity;
import com.aizuda.boot.modules.business.counterparty.entity.ChangeRecord;
import com.aizuda.boot.modules.business.counterparty.entity.CounterpartyInfoEntity;
import com.aizuda.boot.modules.business.counterparty.entity.dto.*;
import com.aizuda.boot.modules.business.counterparty.entity.vo.*;
import com.aizuda.boot.modules.business.counterparty.mapper.BankInfoMapper;
import com.aizuda.boot.modules.business.counterparty.mapper.CounterpartyInfoMapper;
import com.aizuda.boot.modules.business.counterparty.service.BankInfoService;
import com.aizuda.boot.modules.business.counterparty.service.ChangeRecordService;
import com.aizuda.boot.modules.business.counterparty.service.CounterpartyService;
import com.aizuda.boot.modules.common.constant.enums.*;
import com.aizuda.boot.modules.business.dict.service.CountryDictService;
import com.aizuda.boot.modules.file.service.FileInfoService;
import com.aizuda.boot.modules.file.util.FileResponseUtil;
import com.aizuda.boot.modules.system.entity.SysDepartment;
import com.aizuda.boot.modules.system.mapper.SysDepartmentMapper;
import com.aizuda.core.api.ApiAssert;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 交易方服务实现类
 */
@Slf4j
@Service
public class CounterpartyServiceImpl extends ServiceImpl<CounterpartyInfoMapper, CounterpartyInfoEntity> implements CounterpartyService {

    @Resource
    private CounterpartyInfoMapper counterpartyInfoMapper;

    @Resource
    private BankInfoMapper bankInfoMapper;

    @Resource
    private BankInfoService bankInfoService;

    @Resource
    private CountryDictService countryDictService;

    @Resource
    private ChangeRecordService changeRecordService;

    @Resource
    private CounterpartyTenantRelMapper counterpartyTenantRelMapper;

    @Resource
    private FileInfoService fileInfoService;

    @Resource
    private SysDepartmentMapper sysDepartmentMapper;

    @Resource
    private ContractFieldValueMapper contractFieldValueMapper;

    @Override
    public List<CounterpartyInfoEntity> getEnabledCounterparties() {
        return counterpartyInfoMapper.selectList(
                new LambdaQueryWrapper<CounterpartyInfoEntity>()
                        .eq(CounterpartyInfoEntity::getIsEnabled, true)
                        .orderByAsc(CounterpartyInfoEntity::getCounterpartyCode)
        );
    }

    @Override
    public CounterpartyInfoEntity getCounterpartyById(Integer id) {
        return counterpartyInfoMapper.selectById(id);
    }

    @Override
    public boolean toggleCounterpartyStatus(Integer id, Boolean enabled) {
        CounterpartyInfoEntity entity = getById(id);
        if (null == entity) {
            ApiAssert.fail("要更新的交易方不存在");
        }
        if (!Objects.equals(entity.getIsEnabled(), enabled)) {
            UserInfo currentUser = AuthUtil.getCurrentUser();
            entity.setIsEnabled(enabled);
            entity.setUpdateBy(currentUser.getNickName());
            entity.setUpdatedAt(LocalDateTime.now());

            int result = counterpartyInfoMapper.updateById(entity);

            updateStatusRecord(id.longValue(), enabled, currentUser.getId(), currentUser.getNickName());

            if (result > 0) {
                log.info("交易方状态更新成功，ID: {}, 状态: {}", id, enabled ? "启用" : "停用");
                return true;
            } else {
                return false;
            }
        }

        return true;
    }

    private void updateStatusRecord(long id, Boolean enabled, Long userId, String username) {
        Integer currentMaxVersion = getCurrentMaxVersion(id);
        int newVersion = currentMaxVersion == null ? 1 : currentMaxVersion + 1;
        ChangeRecord changeRecord = new ChangeRecord();
        changeRecord.setCounterpartyId(id);
        changeRecord.setField("isEnabled");
        changeRecord.setOldValue(enabled ? "0" : "1");
        changeRecord.setNewValue(enabled ? "1" : "0");
        changeRecord.setVersion(newVersion);
        changeRecord.setCreateId(userId);
        changeRecord.setCreateBy(username);
        changeRecord.setCreateTime(new Date());
        changeRecordService.save(changeRecord);
    }

    @Override
    public CounterpartyPageRespDTO queryCounterpartiesPage(CounterpartyQueryReqDTO reqDTO) {
        // 构建分页对象
        Page<CounterpartyInfoEntity> page = new Page<>(reqDTO.getPageNum(), reqDTO.getPageSize());

        // 构建查询条件
        LambdaQueryWrapper<CounterpartyInfoEntity> queryWrapper = new LambdaQueryWrapper<>();

        // 模糊匹配交易方名称
        if (StringUtils.hasText(reqDTO.getKeyword())) {
            queryWrapper.like(CounterpartyInfoEntity::getCounterpartyName, reqDTO.getKeyword())
                    .or()
                    .like(CounterpartyInfoEntity::getCounterpartyCode, reqDTO.getKeyword())
                    .or()
                    .like(CounterpartyInfoEntity::getCertificateId, reqDTO.getKeyword());
        }
        if (!CollectionUtils.isEmpty(reqDTO.getCounterpartyNature())) {
            queryWrapper.in(CounterpartyInfoEntity::getCounterpartyNature, reqDTO.getCounterpartyNature());
        }
        if (!CollectionUtils.isEmpty(reqDTO.getRegisterCountry())) {
            queryWrapper.in(CounterpartyInfoEntity::getRegisterCountry, reqDTO.getRegisterCountry());
        }
        if (null != reqDTO.getIsEnabled()) {
            queryWrapper.eq(CounterpartyInfoEntity::getIsEnabled, reqDTO.getIsEnabled());
        }
        if (null != reqDTO.getIsRelatedParty()) {
            queryWrapper.eq(CounterpartyInfoEntity::getIsRelatedParty, reqDTO.getIsRelatedParty());
        }
        if (null != reqDTO.getIsRisk()) {
            queryWrapper.eq(CounterpartyInfoEntity::getIsRisk, reqDTO.getIsRisk());
        }
        if (!CollectionUtils.isEmpty(reqDTO.getRiskType())) {
            queryWrapper.in(CounterpartyInfoEntity::getRiskType, reqDTO.getRiskType());
        }
        if (StringUtils.hasText(reqDTO.getSysTenantKey())) {
            List<Integer> list = counterpartyTenantRelMapper.selectList(Wrappers.<CounterpartyTenantRel>lambdaQuery()
                            .eq(CounterpartyTenantRel::getSysTenantKey, reqDTO.getSysTenantKey())
                            .select(CounterpartyTenantRel::getCounterpartyId))
                    .stream().map(CounterpartyTenantRel::getCounterpartyId).toList();
            if (!CollectionUtils.isEmpty(list)) {
                queryWrapper.in(CounterpartyInfoEntity::getId, list);
            } else {
                // 如果没有找到关联记录，返回空的分页结果而不是null
                CounterpartyPageRespDTO respDTO = new CounterpartyPageRespDTO();
                respDTO.setTotal(0L);
                respDTO.setPageNum(reqDTO.getPageNum());
                respDTO.setPageSize(reqDTO.getPageSize());
                respDTO.setTotalPages(0);
                respDTO.setItems(new ArrayList<>());
                return respDTO;
            }

        }

        // 按创建时间倒序排序
        queryWrapper.orderByDesc(CounterpartyInfoEntity::getCreatedAt);

        // 执行分页查询
        IPage<CounterpartyInfoEntity> pageResult = counterpartyInfoMapper.selectPage(page, queryWrapper);

        // 构建响应对象
        CounterpartyPageRespDTO respDTO = new CounterpartyPageRespDTO();
        respDTO.setTotal(pageResult.getTotal());
        respDTO.setPageNum(reqDTO.getPageNum());
        respDTO.setPageSize(reqDTO.getPageSize());
        respDTO.setTotalPages((int) pageResult.getPages());

        // 转换实体列表
        List<CounterpartyPageRespDTO.CounterpartyItemDTO> items = pageResult.getRecords().stream()
                .map(this::convertToItemDTO)
                .collect(Collectors.toList());

        respDTO.setItems(items);

        return respDTO;
    }

    /**
     * 将实体转换为列表项DTO
     */
    private CounterpartyPageRespDTO.CounterpartyItemDTO convertToItemDTO(CounterpartyInfoEntity entity) {
        CounterpartyPageRespDTO.CounterpartyItemDTO itemDTO = new CounterpartyPageRespDTO.CounterpartyItemDTO();
        BeanUtils.copyProperties(entity, itemDTO);

        // 设置枚举code和对应的描述文字
        itemDTO.setCounterpartyNature(entity.getCounterpartyNature());
        itemDTO.setCounterpartyNatureDesc(convertCounterpartyNatureToDesc(entity.getCounterpartyNature()));

        if (StringUtils.hasText(entity.getCounterpartyType())) {
            String[] split = entity.getCounterpartyType().split(",");

            itemDTO.setCounterpartyType(Arrays.stream(split).map(Integer::parseInt).toList());
            List<String> list = Arrays.stream(split).map(counterpartyType -> convertCounterpartyTypeToDesc(Integer.parseInt(counterpartyType))).toList();
            itemDTO.setCounterpartyTypeDesc(list);
        }

        itemDTO.setCertificateType(entity.getCertificateType());
        itemDTO.setCertificateTypeDesc(convertCertificateTypeToDesc(entity.getCertificateType()));

        // 设置国家名称
        itemDTO.setRegisterCountryName(convertCountryCodeToName(entity.getRegisterCountry()));

        // 设置状态文本
        itemDTO.setStatusText(Boolean.TRUE.equals(entity.getIsEnabled()) ? "启用" : "停用");

        // 设置关联方状态文本
        itemDTO.setRelatedPartyText(Boolean.TRUE.equals(entity.getIsRelatedParty()) ? "是" : "否");

        // 设置风险类型描述
        itemDTO.setRiskTypeDesc(convertRiskTypeToDesc(entity.getRiskType()));

        // 处理银行账户信息
        if (entity.getBankInfoIds() != null && !entity.getBankInfoIds().isEmpty()) {
            String[] bankIdStrings = entity.getBankInfoIds().split(",");
            List<Integer> bankIds = new ArrayList<>();
            for (String bankIdStr : bankIdStrings) {
                try {
                    bankIds.add(Integer.parseInt(bankIdStr.trim()));
                } catch (NumberFormatException e) {
                    log.warn("银行ID格式错误: {}", bankIdStr);
                }
            }

            itemDTO.setBankAccountCount(bankIds.size());

            // 查询银行信息
            if (!bankIds.isEmpty()) {
                List<BankInfoEntity> bankInfos = bankInfoMapper.selectByIds(bankIds);
                List<BankInfoDTO> bankInfoDTOs = bankInfos.stream()
                        .map(bank -> {
                            BankInfoDTO bankDTO = new BankInfoDTO();
                            BeanUtils.copyProperties(bank, bankDTO);
                            // 设置银行国家代码和名称
                            bankDTO.setBankCountryCode(bank.getBankCountry()); // 国家代码
                            bankDTO.setBankCountry(bank.getBankCountry()); // 国家代码
                            bankDTO.setBankCountryName(countryDictService.getCountryNameByCode(bank.getBankCountry())); // 国家名称
                            // 设置兼容字段
                            bankDTO.setBankAccount(bank.getAccountNumber()); // 兼容字段，值同accountNumber
                            return bankDTO;
                        })
                        .collect(Collectors.toList());
                itemDTO.setBankInfoList(bankInfoDTOs);
            } else {
                itemDTO.setBankInfoList(new ArrayList<>());
            }
        } else {
            itemDTO.setBankAccountCount(0);
            itemDTO.setBankInfoList(new ArrayList<>());
        }

        if (StringUtils.hasText(entity.getAttachment())) {
            List<Long> fieldIds = Arrays.stream(entity.getAttachment().split(",")).map(Long::parseLong).toList();
            List<CounterpartyPageRespDTO.FileInfoDTO> files = fileInfoService.listByIds(fieldIds)
                    .stream().map(fileInfo -> {
                        CounterpartyPageRespDTO.FileInfoDTO fileInfoDTO = new CounterpartyPageRespDTO.FileInfoDTO();
                        fileInfoDTO.setFileId(fileInfo.getFileId());
                        fileInfoDTO.setFileName(fileInfo.getFileName());
                        fileInfoDTO.setFileType(fileInfo.getFileType());
                        fileInfoDTO.setFileKey(fileInfo.getFileKey());
                        fileInfoDTO.setFileSize(fileInfo.getFileSize());
                        return fileInfoDTO;
                    }).toList();
            itemDTO.setAttachments(files);
        }

        List<String> sysTenantKey = counterpartyTenantRelMapper.selectList(Wrappers.<CounterpartyTenantRel>lambdaQuery()
                        .eq(CounterpartyTenantRel::getCounterpartyId, entity.getId()))
                .stream().map(CounterpartyTenantRel::getSysTenantKey).toList();
        itemDTO.setSysTenantKey(sysTenantKey);
        if (!CollectionUtils.isEmpty(sysTenantKey)) {
            String tenantName = sysDepartmentMapper.selectList(Wrappers.<SysDepartment>lambdaQuery()
                            .in(SysDepartment::getSysTenantKey, sysTenantKey)
                            .and(wrapper -> wrapper.eq(SysDepartment::getPid, 0)
                                    .or()
                                    .eq(SysDepartment::getIndependentFlag, 1)))
                    .stream().map(SysDepartment::getName).collect(Collectors.joining(","));
            itemDTO.setTenantName(tenantName);
        }

        return itemDTO;
    }

    private String convertRiskTypeToDesc(Integer riskType) {
        if (riskType == null) {
            return null;
        }
        return RiskTypeEnum.getByCode(riskType).getDescription();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public CreateCounterpartyRespDTO createCounterparty(CreateCounterpartyReqDTO reqDTO, HttpServletRequest request) {
        if (null == reqDTO || !StringUtils.hasText(reqDTO.getCounterpartyName()) || !StringUtils.hasText(reqDTO.getCertificateId())) {
            ApiAssert.fail("请填写交易方名称和证件ID");
        }
        if (CollectionUtils.isEmpty(reqDTO.getSysTenantKey())) {
            ApiAssert.fail("租户不能为空，请选择交易方适用的租户");
        }
        if (reqDTO.getSysTenantKey().size() > 1) {
            ApiAssert.fail("请选择一个租户");
        }
        int count = counterpartyInfoMapper.checkCounterparty(null, reqDTO.getCounterpartyName(), reqDTO.getCertificateId(), reqDTO.getSysTenantKey().get(0));
        if (count > 0) {
            ApiAssert.fail("交易方已存在");
        }
        // 判断客户端类型
        String clientType = determineClientType(request);
        log.info("创建交易方 - 客户端类型: {}", clientType);

        // 创建交易方实体对象
        CounterpartyInfoEntity entity = new CounterpartyInfoEntity();

        // 设置基本信息
        entity.setCounterpartyCode(reqDTO.getCounterpartyCode() != null ?
                reqDTO.getCounterpartyCode() : generateCounterpartyCode());
        entity.setCounterpartyName(reqDTO.getCounterpartyName());
        entity.setRegisterCountry(reqDTO.getRegisterCountry());

        // 转换交易方性质：描述文字 -> 枚举code
        entity.setCounterpartyNature(reqDTO.getCounterpartyNature());

        // 转换交易方类型：描述文字 -> 枚举code  
        entity.setCounterpartyType(reqDTO.getCounterpartyType().stream().map(String::valueOf).collect(Collectors.joining(",")));

        entity.setIsRelatedParty(reqDTO.getIsRelatedParty() != null ? reqDTO.getIsRelatedParty() : false);

        // 转换证件类型：描述文字 -> 枚举code
        entity.setCertificateType(reqDTO.getCertificateType());

        entity.setCertificateId(reqDTO.getCertificateId());

        UserInfo currentUser = AuthUtil.getCurrentUser();


        entity.setIsEnabled(true);
        entity.setCreateAppid(clientType);
        entity.setCreateSource(clientType);
        entity.setUpdateAppid(clientType);
        entity.setUpdateSource(clientType);

        entity.setCreateId(Long.parseLong(currentUser.getUserId()));
        entity.setCreateBy(currentUser.getNickName());
        entity.setCreatedAt(LocalDateTime.now());
        entity.setUpdateBy(currentUser.getNickName());
        entity.setUpdatedAt(LocalDateTime.now());

        if (!CollectionUtils.isEmpty(reqDTO.getAttachments())) {
            entity.setAttachment(reqDTO.getAttachments().stream().map(String::valueOf).collect(Collectors.joining(",")));
        }

        // 保存交易方到数据库
        int result = counterpartyInfoMapper.insert(entity);
        if (result <= 0) {
            throw new RuntimeException("交易方创建失败");
        }

        log.info("交易方创建成功，ID: {}, 名称: {}", entity.getId(), entity.getCounterpartyName());

        // 绑定交易方与部门租户
        bindingDepartment(entity.getId(), reqDTO.getSysTenantKey());

        // 创建银行信息并绑定到交易方
        List<CreateBankInfoRespDTO> bankInfoRespList = createAndBindBankInfos(reqDTO.getBankInfoList(), entity.getId());

        // 构造响应DTO
        CreateCounterpartyRespDTO respDTO = new CreateCounterpartyRespDTO();
        respDTO.setId(entity.getId());
        respDTO.setCounterpartyCode(entity.getCounterpartyCode());
        respDTO.setCounterpartyName(entity.getCounterpartyName());
        respDTO.setRegisterCountry(entity.getRegisterCountry());

        // 设置国家名称
        respDTO.setRegisterCountryName(convertCountryCodeToName(entity.getRegisterCountry()));

        // 设置枚举code和对应的描述文字
        respDTO.setCounterpartyNature(entity.getCounterpartyNature());
        respDTO.setCounterpartyNatureDesc(convertCounterpartyNatureToDesc(entity.getCounterpartyNature()));

        List<Integer> list = Arrays.stream(entity.getCounterpartyType().split(",")).map(Integer::parseInt).toList();
        respDTO.setCounterpartyType(list);
        respDTO.setCounterpartyTypeDesc(list.stream().map(this::convertCounterpartyTypeToDesc).toList());

        respDTO.setIsRelatedParty(entity.getIsRelatedParty());

        respDTO.setCertificateType(entity.getCertificateType());
        respDTO.setCertificateTypeDesc(convertCertificateTypeToDesc(entity.getCertificateType()));

        respDTO.setCertificateId(entity.getCertificateId());
        respDTO.setBankInfoList(bankInfoRespList);
        respDTO.setIsEnabled(entity.getIsEnabled());
        respDTO.setCreatedAt(entity.getCreatedAt().toString());

        return respDTO;
    }

    private void bindingDepartment(Integer counterpartyId, List<String> sysTenantKey) {
        if (CollectionUtils.isEmpty(sysTenantKey)) {
            return;
        }
        UserInfo currentUser = AuthUtil.getCurrentUser();
        if (null == currentUser || null == currentUser.getId()) {
            return;
        }
        sysTenantKey.forEach(item -> {
            CounterpartyTenantRel entity = new CounterpartyTenantRel();
            entity.setCounterpartyId(counterpartyId);
            entity.setSysTenantKey(item);
            entity.setCreateId(currentUser.getId());
            entity.setCreateBy(currentUser.getNickName());
            entity.setCreatedAt(new Date());
            entity.setDeleted(false);
            counterpartyTenantRelMapper.insert(entity);
        });

    }

    /**
     * 创建银行信息并绑定到交易方
     *
     * @param bankInfoList   银行信息列表
     * @param counterpartyId 交易方ID
     * @return 创建的银行信息响应列表
     */
    private List<CreateBankInfoRespDTO> createAndBindBankInfos(List<BankInfoDTO> bankInfoList, Integer counterpartyId) {
        List<CreateBankInfoRespDTO> respList = new ArrayList<>();

        if (bankInfoList == null || bankInfoList.isEmpty()) {
            return respList;
        }

        for (int i = 0; i < bankInfoList.size(); i++) {
            BankInfoDTO bankInfo = bankInfoList.get(i);
            // 转换为创建银行信息的请求DTO
            CreateBankInfoReqDTO bankReqDTO = new CreateBankInfoReqDTO();
            int number = i + 1;
            bankReqDTO.setBankCode("银行账户" + number);
            bankReqDTO.setBankCountry(bankInfo.getBankCountry());
            bankReqDTO.setBankName(bankInfo.getBankName());
            bankReqDTO.setAccountName(bankInfo.getAccountName());
            bankReqDTO.setAccountNumber(bankInfo.getAccountNumber());
            bankReqDTO.setBankInternalId(bankInfo.getBankInternalId());
            bankReqDTO.setCurrency(bankInfo.getCurrency());
            bankReqDTO.setGeneralLedgerAccount(bankInfo.getGeneralLedgerAccount());
            bankReqDTO.setClearingAccount(bankInfo.getClearingAccount());
            bankReqDTO.setAccountAttributeDesc(bankInfo.getAccountAttributeDesc());

            // 设置绑定信息
            bankReqDTO.setBindingType("交易方");
            bankReqDTO.setBindingId(counterpartyId.toString());

            // 创建银行信息
            CreateBankInfoRespDTO bankRespDTO = bankInfoService.createBankInfo(bankReqDTO);
            respList.add(bankRespDTO);
        }

        return respList;
    }

    /**
     * 生成交易方编码
     *
     * @return 交易方编码
     */
    private String generateCounterpartyCode() {
        // 简单实现：使用时间戳 + 随机数
        return "CP" + System.currentTimeMillis() + String.format("%03d", (int) (Math.random() * 1000));
    }

    // ==================== 枚举转换方法 ====================

    /**
     * 将交易方性质code转换为描述
     *
     * @param natureCode 性质code
     * @return 性质描述
     */
    private String convertCounterpartyNatureToDesc(Integer natureCode) {
        if (natureCode == null) {
            return null;
        }
        CounterpartyNatureEnum natureEnum = CounterpartyNatureEnum.getByCode(natureCode);
        return natureEnum != null ? natureEnum.getName() : null;
    }

    /**
     * 将交易方类型code转换为描述
     *
     * @param typeCode 类型code
     * @return 类型描述
     */
    private String convertCounterpartyTypeToDesc(Integer typeCode) {
        if (typeCode == null) {
            return null;
        }
        CounterpartyTypeEnum typeEnum = CounterpartyTypeEnum.getByCode(typeCode);
        return typeEnum != null ? typeEnum.getDesc() : null;
    }

    /**
     * 将证件类型code转换为描述
     *
     * @param certTypeCode 证件类型code
     * @return 证件类型描述
     */
    private String convertCertificateTypeToDesc(Integer certTypeCode) {
        if (certTypeCode == null) {
            return null;
        }
        CertificateTypeEnum certEnum = CertificateTypeEnum.getByCode(certTypeCode);
        return certEnum != null ? certEnum.getName() : null;
    }

    /**
     * 将国家代码转换为国家名称
     *
     * @param countryCode 国家代码
     * @return 国家名称
     */
    private String convertCountryCodeToName(String countryCode) {
        if (countryCode == null) {
            return null;
        }
        return countryDictService.getCountryNameByCode(countryCode);
    }

    @Override
    public boolean checkCounterpartyUniqueness(String counterpartyName, String registerCountry, Integer counterpartyNature) {
        log.info("检查交易方唯一性 - 名称: {}, 国家: {}, 性质: {}", counterpartyName, registerCountry, counterpartyNature);

        // 构建查询条件
        LambdaQueryWrapper<CounterpartyInfoEntity> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(CounterpartyInfoEntity::getCounterpartyName, counterpartyName)
                .eq(CounterpartyInfoEntity::getRegisterCountry, registerCountry)
                .eq(CounterpartyInfoEntity::getCounterpartyNature, counterpartyNature);

        // 查询是否存在符合条件的记录
        Long count = counterpartyInfoMapper.selectCount(queryWrapper);

        boolean isUnique = count == null || count == 0L;
        log.info("交易方唯一性检查结果 - 是否唯一: {}, 查询到的记录数: {}", isUnique, count);

        return isUnique;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public Boolean updateCounterparty(UpdateCounterpartyReqDTO reqDTO, HttpServletRequest request) {
        if (reqDTO == null || reqDTO.getId() == null || !StringUtils.hasText(reqDTO.getCounterpartyName()) || !StringUtils.hasText(reqDTO.getCertificateId())) {
            ApiAssert.fail("参数错误");
        }
        Integer id = reqDTO.getId();
        CounterpartyInfoEntity entity = this.getById(id);
        if (null == entity) {
            ApiAssert.fail("交易方不存在");
        }

        if (CollectionUtils.isEmpty(reqDTO.getSysTenantKey())) {
            ApiAssert.fail("租户不能为空，请选择交易方适用的租户");
        }
        if (reqDTO.getSysTenantKey().size() > 1) {
            ApiAssert.fail("请选择一个租户");
        }

        long count = counterpartyInfoMapper.checkCounterparty(id, reqDTO.getCounterpartyName(), reqDTO.getCertificateId(), reqDTO.getSysTenantKey().get(0));
        if (count > 0) {
            ApiAssert.fail("交易方重复");
        }

        UserInfo currentUser = AuthUtil.getCurrentUser();
        // 判断客户端类型
        String clientType = determineClientType(request);
        log.info("创建交易方 - 客户端类型: {}", clientType);

        compareAndSaveChangeRecordByDto(entity, reqDTO, reqDTO.getId().longValue(), Long.parseLong(currentUser.getUserId()), currentUser.getNickName());

        // 设置基本信息
        entity.setCounterpartyCode(reqDTO.getCounterpartyCode() != null ?
                reqDTO.getCounterpartyCode() : generateCounterpartyCode());
        entity.setCounterpartyName(reqDTO.getCounterpartyName());
        entity.setRegisterCountry(reqDTO.getRegisterCountry());

        // 转换交易方性质：描述文字 -> 枚举code
        entity.setCounterpartyNature(reqDTO.getCounterpartyNature());

        // 转换交易方类型：描述文字 -> 枚举code
        entity.setCounterpartyType(reqDTO.getCounterpartyType().stream().map(String::valueOf).collect(Collectors.joining(",")));

        entity.setIsRelatedParty(reqDTO.getIsRelatedParty() != null ? reqDTO.getIsRelatedParty() : false);

        // 转换证件类型：描述文字 -> 枚举code
        entity.setCertificateType(reqDTO.getCertificateType());

        entity.setCertificateId(reqDTO.getCertificateId());

        entity.setIsEnabled(entity.getIsEnabled() != null ? entity.getIsEnabled() : true);

        entity.setUpdateAppid(clientType);
        entity.setUpdateSource(clientType);

        entity.setUpdateBy(currentUser.getNickName());
        entity.setUpdatedAt(LocalDateTime.now());

        if (!CollectionUtils.isEmpty(reqDTO.getAttachments())) {
            entity.setAttachment(reqDTO.getAttachments().stream().map(String::valueOf).collect(Collectors.joining(",")));
        } else {
            entity.setAttachment(null);
        }

        String bankInfoIds = entity.getBankInfoIds();

        entity.setBankInfoIds(null);

        // 保存交易方到数据库
        boolean result = this.updateById(entity);

        if (!result) {
            throw new RuntimeException("交易方创建失败");
        }

        // 更新交易方租户部门关联
        counterpartyTenantRelMapper.delete(Wrappers.<CounterpartyTenantRel>lambdaQuery()
                .eq(CounterpartyTenantRel::getCounterpartyId, id));
        bindingDepartment(id, reqDTO.getSysTenantKey());

        if (StringUtils.hasText(bankInfoIds)) {
            List<Integer> bankIds = Arrays.stream(bankInfoIds.split(",")).map(Integer::parseInt).collect(Collectors.toList());
            if (!CollectionUtils.isEmpty(bankIds)) {
                bankInfoMapper.deleteByIds(bankIds);
            }
        }

        createAndBindBankInfos(reqDTO.getBankInfoList(), entity.getId());

        return true;

    }

    @Override
    public List<ChangeRecordVO> queryChangeRecords(Integer counterpartyId) {
        CounterpartyInfoEntity entity = getById(counterpartyId);
        if (null == entity) {
            return new ArrayList<>();
        }
        return changeRecordService.queryChangeRecords(counterpartyId, "counterparty");
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public SingleResponse<Boolean> uploadCounterparty(MultipartFile file) {
        if (null == file || file.isEmpty()) {
            return SingleResponse.failure(ResponseCodeEnum.FILE_EMPTY.getCode(), ResponseCodeEnum.FILE_EMPTY.getMsg());
        }
        String originalFilename = file.getOriginalFilename();
        if (originalFilename == null || (!originalFilename.endsWith(".xlsx") && !originalFilename.endsWith(".xls"))) {
            return SingleResponse.failure(ResponseCodeEnum.UPLOAD_FILE_ERROR.getCode(), ResponseCodeEnum.UPLOAD_FILE_ERROR.getMsg());
        }

        // 判断文件是否合法
        try (XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream())) {
            XSSFSheet infoSheet = workbook.getSheetAt(0);
            XSSFSheet bankSheet = workbook.getSheetAt(3);
            if (infoSheet.getLastRowNum() < 2 && bankSheet.getLastRowNum() < 2) {
                return SingleResponse.failure(ResponseCodeEnum.FILE_EMPTY.getCode(), ResponseCodeEnum.FILE_EMPTY.getMsg());
            }
            // 验证交易方信息表头
            validateCounterpartyHeaders(infoSheet);

            // 验证银行信息表头
            validateBankHeaders(bankSheet);
        } catch (Exception e) {
            log.error("解析Excel文件失败：{}", e.getMessage());
            return SingleResponse.failure(ResponseCodeEnum.UPLOAD_FILE_ERROR.getCode(), ResponseCodeEnum.UPLOAD_FILE_ERROR.getMsg());
        }

        log.info("开始处理交易方批量上传文件：{}", originalFilename);
        try {
            List<UploadCounterpartyDTO> counterpartyDataList = parseCounterpartyExcelFile(file);
            List<UploadCounterpartyBankDTO> bankDataList = parseBankExcelFile(file);
            UserInfo currentUser = AuthUtil.getCurrentUser();

            ThreadUtil.execute(new Runnable() {
                @Override
                public void run() {
                    Map<ChangeTypeEnum, List<UploadCounterpartyDTO>> counterpartyMap = counterpartyDataList.stream().collect(Collectors.groupingBy(UploadCounterpartyDTO::getType));
                    Map<ChangeTypeEnum, List<UploadCounterpartyBankDTO>> bankMap = bankDataList.stream().collect(Collectors.groupingBy(UploadCounterpartyBankDTO::getType));

                    // 创建序号到交易方ID的映射，用于银行账户关联
                    Map<Long, Integer> serialToCounterpartyIdMap = new HashMap<>();

                    // 预分配版本号，确保同一个交易方的所有操作使用相同版本
                    Map<Long, Integer> counterpartyVersionMap = preAllocateVersions(counterpartyDataList, bankDataList);

                    // 处理交易方数据
                    processCounterpartyData(counterpartyMap, currentUser, serialToCounterpartyIdMap, counterpartyVersionMap);

                    // 处理银行数据
                    processBankData(bankMap, currentUser, serialToCounterpartyIdMap, counterpartyVersionMap);

                    // 处理租户关联
                    processTenantRelations(serialToCounterpartyIdMap, currentUser);
                    log.info("交易方批量上传处理完成，交易方数据{}条，银行数据{}条", counterpartyDataList.size(), bankDataList.size());
                }
            });

            return SingleResponse.of(true);
        } catch (Exception e) {
            log.error("解析Excel文件失败：{}", e.getMessage());
            throw new RuntimeException(e);
        }
    }

    private void processTenantRelations(Map<Long, Integer> serialToCounterpartyIdMap, UserInfo currentUser) {
        String tenantKey = currentUser.getSysTenantVo().getTenantKey();

        // 1. 获取所有交易方ID
        List<Integer> counterpartyIds = serialToCounterpartyIdMap.values().stream()
                .distinct()
                .toList();

        // 2. 先删除原有租户关系
        if (!counterpartyIds.isEmpty()) {
            counterpartyTenantRelMapper.delete(Wrappers.<CounterpartyTenantRel>lambdaQuery()
                    .in(CounterpartyTenantRel::getCounterpartyId, counterpartyIds)
                    .eq(CounterpartyTenantRel::getSysTenantKey, tenantKey));
        }

        // 3. 创建并插入新记录
        List<CounterpartyTenantRel> list = counterpartyIds.stream()
                .map(counterpartyId -> {
                    CounterpartyTenantRel rel = new CounterpartyTenantRel();
                    rel.setCounterpartyId(counterpartyId);
                    rel.setSysTenantKey(tenantKey);
                    rel.setCreateId(currentUser.getId());
                    rel.setCreateBy(currentUser.getNickName());
                    rel.setCreatedAt(new Date());
                    rel.setDeleted(false);
                    return rel;
                })
                .collect(Collectors.toList());

        if (!list.isEmpty()) {
            counterpartyTenantRelMapper.insert(list);
        }
    }

    @Override
    public List<CounterpartyVO> getCounterpartyList() {
        return list(Wrappers.<CounterpartyInfoEntity>lambdaQuery()
                .eq(CounterpartyInfoEntity::getIsEnabled, true))
                .stream().map(item -> {
                    CounterpartyVO vo = new CounterpartyVO();
                    vo.setId(item.getId());
                    vo.setCounterpartyCode(item.getCounterpartyCode());
                    vo.setCounterpartyName(item.getCounterpartyName());
                    return vo;
                }).collect(Collectors.toList());
    }

    @Override
    public CounterpartyPageRespDTO.CounterpartyItemDTO getInfo(String certificateId) {
        if (null == certificateId) {
            return null;
        }
        CounterpartyInfoEntity entity = this.getOne(Wrappers.<CounterpartyInfoEntity>lambdaQuery()
                .eq(CounterpartyInfoEntity::getCertificateId, certificateId)
                .last("limit 1"));
        if (null == entity) {
            return null;
        }
        return convertToItemDTO(entity);
    }

    @Override
    public Boolean deleteCounterparty(Integer counterpartyId) {
        if (null == counterpartyId) {
            return false;
        }
        List<ContractFieldValueEntity> contractFieldValueEntities = contractFieldValueMapper.selectList(Wrappers.<ContractFieldValueEntity>lambdaQuery()
                .eq(ContractFieldValueEntity::getFieldCode, SystemFieldsEnum.OPPOSITE.getCode()));
        contractFieldValueEntities.forEach(item -> {
            String fieldValue = item.getFieldValue();
            JSONArray array = JSON.parseArray(fieldValue);
            if (array.contains(counterpartyId)) {
                ApiAssert.fail("交易方实体已经被关联，不允许删除！");
            }
        });
        CounterpartyInfoEntity counterpartyInfo = this.getById(counterpartyId);
        if (null == counterpartyInfo) {
            return false;
        }
        // 删除交易方租户关联
        counterpartyTenantRelMapper.delete(new LambdaQueryWrapper<CounterpartyTenantRel>()
                .eq(CounterpartyTenantRel::getCounterpartyId, counterpartyId));
        // 删除银行关联
        String bankInfoIds = counterpartyInfo.getBankInfoIds();
        if (StringUtils.hasText(bankInfoIds)) {
            List<String> bankIds = Arrays.stream(bankInfoIds.split(",")).toList();
            if (!CollectionUtils.isEmpty(bankIds)) {
                bankInfoMapper.deleteByIds(bankIds);
            }
        }
        return this.removeById(counterpartyId);
    }

    @Override
    public void exportCounterparty(ExportDTO dto, HttpServletResponse response) {
        if (null == dto || CollectionUtils.isEmpty(dto.getIds())) {
            log.info("导出交易方数据为空");
            FileResponseUtil.write(response, SingleResponse.failure(ResponseCodeEnum.PARAM_BINDING_EXCEPTION.getCode(), ResponseCodeEnum.PARAM_BINDING_EXCEPTION.getMsg()));
            return;
        }
        // 查询要导出的交易方数据
        List<CounterpartyInfoEntity> counterpartyInfoEntities = this.lambdaQuery()
                .in(CounterpartyInfoEntity::getId, dto.getIds())
                .list();
        if (CollectionUtils.isEmpty(counterpartyInfoEntities)){
            log.info("查询不到交易方数据");
            FileResponseUtil.write(response, SingleResponse.failure(ResponseCodeEnum.DATA_NOT_FOUND.getCode(), ResponseCodeEnum.DATA_NOT_FOUND.getMsg()));
            return;
        }
        
        Map<Integer, Integer> bankIdToCounterpartyId = new HashMap<>();
        Set<Integer> bankIds = new HashSet<>();
        for (CounterpartyInfoEntity entity : counterpartyInfoEntities) {
            if (StringUtils.hasText(entity.getBankInfoIds())) {
                String[] idArr = entity.getBankInfoIds().split(",");
                for (String idStr : idArr) {
                    try {
                        Integer bankId = Integer.parseInt(idStr.trim());
                        bankIds.add(bankId);
                        bankIdToCounterpartyId.put(bankId, entity.getId());
                    } catch (NumberFormatException ignored) {
                    }
                }
            }
        }

        List<BankInfoEntity> bankInfos = new ArrayList<>();
        if (!bankIds.isEmpty()) {
            bankInfos = bankInfoMapper.selectByIds(new ArrayList<>(bankIds));
        }

        // 为导出建立counterpartyId -> CounterpartyInfoEntity映射，及序号映射
        Map<Integer, CounterpartyInfoEntity> idToCounterparty = counterpartyInfoEntities.stream()
                .collect(Collectors.toMap(CounterpartyInfoEntity::getId, e -> e));
        Map<Integer, Integer> counterpartyIdToSerial = new HashMap<>();
        for (int i = 0; i < counterpartyInfoEntities.size(); i++) {
            counterpartyIdToSerial.put(counterpartyInfoEntities.get(i).getId(), i + 1);
        }

        // 创建新的excel文件并写入数据
        try (XSSFWorkbook workbook = new XSSFWorkbook()) {
            // ========= 样式与字体 =========
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setFontHeightInPoints((short) 14);

            Font dataFont = workbook.createFont();
            dataFont.setFontHeightInPoints((short) 12);

            CellStyle headerStyle = workbook.createCellStyle();
            headerStyle.setFont(headerFont);
            headerStyle.setAlignment(HorizontalAlignment.CENTER);
            headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            CellStyle dataStyle = workbook.createCellStyle();
            dataStyle.setFont(dataFont);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 创建交易方sheet并写入表头
            XSSFSheet cpSheet = workbook.createSheet("交易方信息");
            cpSheet.setDefaultRowHeightInPoints(20f);
            String[] cpHeaders = {"基础信息序号", "交易方编码", "交易方名称", "注册国家/地区", "交易方性质", "交易方类型", "是否关联方", "证件类型", "证件ID"};
            Row cpHeaderRow = cpSheet.createRow(0);
            cpHeaderRow.setHeightInPoints(24f);
            for (int i = 0; i < cpHeaders.length; i++) {
                Cell cell = cpHeaderRow.createCell(i);
                cell.setCellValue(cpHeaders[i]);
                cell.setCellStyle(headerStyle);
            }

            // 写入交易方数据
            for (int i = 0; i < counterpartyInfoEntities.size(); i++) {
                CounterpartyInfoEntity e = counterpartyInfoEntities.get(i);
                Row row = cpSheet.createRow(i + 1);
                row.setHeightInPoints(20f);
                int col = 0;
                Cell c0 = row.createCell(col++);
                c0.setCellValue(i + 1);
                c0.setCellStyle(dataStyle);
                Cell c1 = row.createCell(col++);
                c1.setCellValue(e.getCounterpartyCode() == null ? "" : e.getCounterpartyCode());
                c1.setCellStyle(dataStyle);
                Cell c2 = row.createCell(col++);
                c2.setCellValue(e.getCounterpartyName() == null ? "" : e.getCounterpartyName());
                c2.setCellStyle(dataStyle);
                String countryName = CountryEnum.getCountryName(e.getRegisterCountry());
                Cell c3 = row.createCell(col++);
                c3.setCellValue(countryName == null ? "" : countryName);
                c3.setCellStyle(dataStyle);
                String natureDesc = convertCounterpartyNatureToDesc(e.getCounterpartyNature());
                Cell c4 = row.createCell(col++);
                c4.setCellValue(natureDesc == null ? "" : natureDesc);
                c4.setCellStyle(dataStyle);

                String typeDesc = "";
                if (StringUtils.hasText(e.getCounterpartyType())) {
                    List<String> typeDescs = Arrays.stream(e.getCounterpartyType().split(","))
                            .map(String::trim)
                            .filter(str -> !str.isEmpty())
                            .map(Integer::parseInt)
                            .map(this::convertCounterpartyTypeToDesc)
                            .filter(Objects::nonNull)
                            .toList();
                    typeDesc = String.join(",", typeDescs);
                }
                Cell c5 = row.createCell(col++);
                c5.setCellValue(typeDesc);
                c5.setCellStyle(dataStyle);

                Cell c6 = row.createCell(col++);
                c6.setCellValue(Boolean.TRUE.equals(e.getIsRelatedParty()) ? "是" : "否");
                c6.setCellStyle(dataStyle);
                String certTypeDesc = convertCertificateTypeToDesc(e.getCertificateType());
                Cell c7 = row.createCell(col++);
                c7.setCellValue(certTypeDesc == null ? "" : certTypeDesc);
                c7.setCellStyle(dataStyle);
                Cell c8 = row.createCell(col);
                c8.setCellValue(e.getCertificateId() == null ? "" : e.getCertificateId());
                c8.setCellStyle(dataStyle);
            }

            // 创建银行sheet并写入表头
            XSSFSheet bankSheet = workbook.createSheet("银行信息");
            bankSheet.setDefaultRowHeightInPoints(20f);
            String[] bankHeaders = {"账户信息 ID", "基础信息序号", "交易方编码", "账号", "账户名", "银行内部ID", "银行名称", "银行国家"};
            Row bankHeaderRow = bankSheet.createRow(0);
            bankHeaderRow.setHeightInPoints(24f);
            for (int i = 0; i < bankHeaders.length; i++) {
                Cell cell = bankHeaderRow.createCell(i);
                cell.setCellValue(bankHeaders[i]);
                cell.setCellStyle(headerStyle);
            }

            // 写入银行数据
            int bankRowIdx = 1;
            for (BankInfoEntity bank : bankInfos) {
                Integer cpId = bankIdToCounterpartyId.get(bank.getId());
                CounterpartyInfoEntity cp = cpId != null ? idToCounterparty.get(cpId) : null;
                if (cp == null) {
                    continue;
                }
                Row row = bankSheet.createRow(bankRowIdx++);
                row.setHeightInPoints(20f);
                int col = 0;
                Cell b0 = row.createCell(col++);
                b0.setCellValue(bank.getId() == null ? "" : String.valueOf(bank.getId()));
                b0.setCellStyle(dataStyle);
                Cell b1 = row.createCell(col++);
                b1.setCellValue(counterpartyIdToSerial.getOrDefault(cp.getId(), 0));
                b1.setCellStyle(dataStyle);
                Cell b2 = row.createCell(col++);
                b2.setCellValue(cp.getCounterpartyCode() == null ? "" : cp.getCounterpartyCode());
                b2.setCellStyle(dataStyle);
                Cell b3 = row.createCell(col++);
                b3.setCellValue(bank.getAccountNumber() == null ? "" : bank.getAccountNumber());
                b3.setCellStyle(dataStyle);
                Cell b4 = row.createCell(col++);
                b4.setCellValue(bank.getAccountName() == null ? "" : bank.getAccountName());
                b4.setCellStyle(dataStyle);
                Cell b5 = row.createCell(col++);
                b5.setCellValue(bank.getBankInternalId() == null ? "" : bank.getBankInternalId());
                b5.setCellStyle(dataStyle);
                Cell b6 = row.createCell(col++);
                b6.setCellValue(bank.getBankName() == null ? "" : bank.getBankName());
                b6.setCellStyle(dataStyle);
                String bankCountryName = CountryEnum.getCountryName(bank.getBankCountry());
                Cell b7 = row.createCell(col);
                b7.setCellValue(bankCountryName == null ? "" : bankCountryName);
                b7.setCellStyle(dataStyle);
            }

            // 自动列宽并适当放大列宽
            for (int i = 0; i < 9; i++) {
                cpSheet.autoSizeColumn(i);
                int width = cpSheet.getColumnWidth(i);
                int minWidth = 20 * 256; // 至少20个字符宽
                int target = Math.max(width, minWidth);
                // 再额外放大一些以便阅读
                target = Math.min(target + 1024, 255 * 256);
                cpSheet.setColumnWidth(i, target);
            }
            for (int i = 0; i < 8; i++) {
                bankSheet.autoSizeColumn(i);
                int width = bankSheet.getColumnWidth(i);
                int minWidth = 18 * 256; // 至少18个字符宽
                int target = Math.max(width, minWidth);
                target = Math.min(target + 1024, 255 * 256);
                bankSheet.setColumnWidth(i, target);
            }

            // 输出到响应
            String fileName = "交易方数据导出.xlsx";
            try (java.io.ByteArrayOutputStream bos = new java.io.ByteArrayOutputStream()) {
                workbook.write(bos);
                bos.flush();
                byte[] bytes = bos.toByteArray();
                FileResponseUtil.setDownloadFileHeader(response, fileName, (long) bytes.length);
                response.getOutputStream().write(bytes);
                response.flushBuffer();
            }
        } catch (Exception e) {
            log.error("导出交易方失败", e);
            FileResponseUtil.write(response, SingleResponse.failure(ResponseCodeEnum.ERROR.getCode(), ResponseCodeEnum.ERROR.getMsg()));
        }

    }

    private List<UploadCounterpartyBankDTO> parseBankExcelFile(MultipartFile file) {
        ArrayList<UploadCounterpartyBankDTO> dataList = new ArrayList<>();
        try (XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream())) {
            XSSFSheet sheet = workbook.getSheetAt(3);

            // 跳过表头和模版提示
            for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (null == row) continue;

                UploadCounterpartyBankDTO data = parseRowToBank(row, i + 1);
                if (null != data) {
                    dataList.add(data);
                }
            }
        } catch (Exception e) {
            log.error("解析Excel文件失败：{}", e.getMessage());
            return dataList;
        }
        return dataList;
    }

    private UploadCounterpartyBankDTO parseRowToBank(Row row, int rowNum) {
        try {
            UploadCounterpartyBankDTO result = new UploadCounterpartyBankDTO();

            // 变更类型
            Cell typeCell = row.getCell(0);
            if (typeCell != null) {
                result.setType(ChangeTypeEnum.getByDesc(typeCell.getStringCellValue()));
            }
            // 账户信息ID
            Cell idCell = row.getCell(1);
            result.setId(idCell == null ? null : (int) idCell.getNumericCellValue());
            // 基础信息序号
            Cell serialCell = row.getCell(2);
            result.setSerialNumber(serialCell == null ? null : (long) serialCell.getNumericCellValue());
            // 交易方编码
            Cell codeCell = row.getCell(3);
            result.setCounterpartyCode(codeCell == null ? null : codeCell.getStringCellValue());
            // 账号
            Cell accountCell = row.getCell(4);
            if (accountCell != null) {
                result.setAccountNumber(accountCell.getStringCellValue());
            }
            // 账号名
            Cell nameCell = row.getCell(5);
            if (nameCell != null) {
                result.setAccountName(nameCell.getStringCellValue());
            }
            // 银行内部唯一ID
            Cell internalIdCell = row.getCell(6);
            result.setBankInternalId(internalIdCell == null ? null : internalIdCell.getStringCellValue());
            // 银行名称
            Cell bankNameCell = row.getCell(7);
            if (bankNameCell != null) {
                result.setBankName(bankNameCell.getStringCellValue());
            }
            // 银行国家
            Cell countryCell = row.getCell(8);
            if (countryCell != null) {
                result.setBankCountry(CountryEnum.getCountryCode(countryCell.getStringCellValue()));
            }

            return result;
        } catch (Exception e) {
            log.warn("解析第{}行数据失败：{}", rowNum, e.getMessage());
            return null;
        }
    }

    private List<UploadCounterpartyDTO> parseCounterpartyExcelFile(MultipartFile file) {
        ArrayList<UploadCounterpartyDTO> dataList = new ArrayList<>();

        try (XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream())) {
            XSSFSheet sheet = workbook.getSheetAt(0);

            // 跳过表头和模版提示，从第三行开始读取
            for (int i = 2; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                UploadCounterpartyDTO data = parseRowToCounterparty(row, i + 1);
                if (data != null) {
                    dataList.add(data);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return dataList;
    }

    private UploadCounterpartyDTO parseRowToCounterparty(Row row, int rowNum) {
        try {
            UploadCounterpartyDTO result = new UploadCounterpartyDTO();
            // 变更类型
            Cell typeCell = row.getCell(0);
            if (typeCell != null) {
                result.setType(ChangeTypeEnum.getByDesc(typeCell.getStringCellValue()));
            }
            // 基础信息序号
            Cell serialCell = row.getCell(1);
            result.setSerialNumber(serialCell == null ? null : (long) serialCell.getNumericCellValue());
            // 交易方编码
            Cell codeCell = row.getCell(2);
            result.setCounterpartyCode(codeCell == null ? null : codeCell.getStringCellValue());
            // 交易方名称
            Cell nameCell = row.getCell(3);
            if (nameCell != null) {
                result.setCounterpartyName(nameCell.getStringCellValue());
            }
            // 注册国家/地区
            Cell countryCell = row.getCell(4);
            if (countryCell != null) {
                result.setRegisterCountry(CountryEnum.getCountryCode(countryCell.getStringCellValue()));
            }
            // 交易方性质
            Cell natureCell = row.getCell(5);
            if (natureCell != null) {
                result.setCounterpartyNature(CounterpartyNatureEnum.getByName(natureCell.getStringCellValue()));
            }
            // 交易方类型
            Cell typeListCell = row.getCell(6);
            if (typeListCell == null) {
                result.setCounterpartyType(null);
            } else {
                String counterpartyType = typeListCell.getStringCellValue();
                if (StringUtils.hasText(counterpartyType)) {
                    counterpartyType = Arrays.stream(counterpartyType.split(","))
                            .map(CounterpartyTypeEnum::getByDesc)
                            .filter(Objects::nonNull)
                            .map(CounterpartyTypeEnum::getCode)
                            .map(String::valueOf)
                            .collect(Collectors.joining(","));
                }
                result.setCounterpartyType(counterpartyType);
            }
            // 是否关联方
            Cell relatedCell = row.getCell(7);
            result.setIsRelatedParty(relatedCell == null ? null : relatedCell.getStringCellValue().equals("是"));
            // 证件类型
            Cell certTypeCell = row.getCell(8);
            result.setCertificateType(certTypeCell == null ? null : CertificateTypeEnum.getCodeByName(certTypeCell.getStringCellValue()));
            // 证件ID
            Cell certIdCell = row.getCell(9);
            result.setCertificateId(certIdCell == null ? null : certIdCell.getStringCellValue());
            return result;
        } catch (Exception e) {
            log.warn("解析第{}行数据失败：{}", rowNum, e.getMessage());
            return null;
        }
    }

    private void compareAndSaveChangeRecordByDto(CounterpartyInfoEntity entity, UpdateCounterpartyReqDTO reqDTO, long counterpartyId, long userId, String username) {
        // 获取当前最大版本号
        Integer currentMaxVersion = getCurrentMaxVersion(counterpartyId);
        Integer newVersion = (currentMaxVersion != null ? currentMaxVersion : 0) + 1;

        // 比较字段并保存修改记录
        Map<String, Map<String, String>> fieldChanges = compareFieldsByDto(entity, reqDTO);
        Map<String, List<String>> bankChanges = compareBanks(entity, reqDTO);
        Map<String, List<String>> attachmentChanges = compareAttachments(entity, reqDTO);

        // 判断是否有字段变更、银行账户变更或附件变更
        boolean hasFieldChanges = !fieldChanges.isEmpty();
        List<String> bankAddList = bankChanges.get("addList");
        List<String> bankDeleteList = bankChanges.get("deleteList");
        boolean hasBankChanges = (bankAddList != null && !bankAddList.isEmpty()) || (bankDeleteList != null && !bankDeleteList.isEmpty());

        List<String> attachmentAddList = attachmentChanges.get("addList");
        List<String> attachmentDeleteList = attachmentChanges.get("deleteList");
        boolean hasAttachmentChanges = (attachmentAddList != null && !attachmentAddList.isEmpty()) || (attachmentDeleteList != null && !attachmentDeleteList.isEmpty());

        if (hasFieldChanges || hasBankChanges || hasAttachmentChanges) {
            List<ChangeRecord> changeRecords = new ArrayList<>();

            if (hasFieldChanges) {
                // 处理字段变更记录
                for (Map.Entry<String, Map<String, String>> entry : fieldChanges.entrySet()) {
                    String fieldName = entry.getKey();
                    Map<String, String> values = entry.getValue();

                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setCounterpartyId(counterpartyId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField(fieldName);
                    changeRecord.setNewValue(values.get("newValue"));
                    changeRecord.setOldValue(values.get("oldValue"));
                    changeRecord.setCreateId(userId);
                    changeRecord.setCreateBy(username);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            // 处理银行账户变更记录
            if (bankAddList != null) {
                for (String accountNumber : bankAddList) {
                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setCounterpartyId(counterpartyId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField("bank");
                    changeRecord.setOldValue(null);
                    changeRecord.setNewValue(accountNumber);
                    changeRecord.setCreateId(userId);
                    changeRecord.setCreateBy(username);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            if (bankDeleteList != null) {
                for (String accountNumber : bankDeleteList) {
                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setCounterpartyId(counterpartyId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField("bank");
                    changeRecord.setOldValue(accountNumber);
                    changeRecord.setNewValue(null);
                    changeRecord.setCreateId(userId);
                    changeRecord.setCreateBy(username);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            // 处理附件变更记录
            if (attachmentAddList != null) {
                for (String fileName : attachmentAddList) {
                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setCounterpartyId(counterpartyId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField("attachment");
                    changeRecord.setOldValue(null);
                    changeRecord.setNewValue(fileName);
                    changeRecord.setCreateId(userId);
                    changeRecord.setCreateBy(username);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            if (attachmentDeleteList != null) {
                for (String fileName : attachmentDeleteList) {
                    ChangeRecord changeRecord = new ChangeRecord();
                    changeRecord.setCounterpartyId(counterpartyId);
                    changeRecord.setVersion(newVersion);
                    changeRecord.setField("attachment");
                    changeRecord.setOldValue(fileName);
                    changeRecord.setNewValue(null);
                    changeRecord.setCreateId(userId);
                    changeRecord.setCreateBy(username);
                    changeRecord.setCreateTime(new Date());

                    changeRecords.add(changeRecord);
                }
            }

            // 批量保存修改记录
            if (!changeRecords.isEmpty()) {
                changeRecordService.saveBatch(changeRecords);
                log.info("保存修改记录成功，交易方ID: {}, 版本号: {}, 变更记录数: {} (字段变更: {}, 银行新增: {}, 银行删除: {}, 附件新增: {}, 附件删除: {})",
                        counterpartyId, newVersion, changeRecords.size(), fieldChanges.size(),
                        bankAddList != null ? bankAddList.size() : 0, bankDeleteList != null ? bankDeleteList.size() : 0,
                        attachmentAddList != null ? attachmentAddList.size() : 0, attachmentDeleteList != null ? attachmentDeleteList.size() : 0);
            }
        }
    }

    private Map<String, List<String>> compareAttachments(CounterpartyInfoEntity entity, UpdateCounterpartyReqDTO reqDTO) {
        ArrayList<String> addList = new ArrayList<>();
        ArrayList<String> deleteList = new ArrayList<>();
        HashMap<String, List<String>> result = new HashMap<>();

        // 获取数据库中的附件文件名
        List<String> oldAttachmentNames = new ArrayList<>();
        if (StringUtils.hasText(entity.getAttachment())) {
            List<Long> oldAttachmentIds = Arrays.stream(entity.getAttachment().split(","))
                    .map(Long::parseLong)
                    .toList();
            // 通过文件ID获取文件名
            oldAttachmentNames = fileInfoService.getFileNamesByIds(oldAttachmentIds);
        }

        // 获取新的附件文件名
        List<String> newAttachmentNames = new ArrayList<>();
        if (reqDTO.getAttachments() != null && !reqDTO.getAttachments().isEmpty()) {
            List<Long> newAttachmentIds = reqDTO.getAttachments();
            newAttachmentNames = fileInfoService.getFileNamesByIds(newAttachmentIds);
        }

        // 找出删除的附件：数据库中有，现在没有
        for (String oldAttachment : oldAttachmentNames) {
            if (!newAttachmentNames.contains(oldAttachment)) {
                deleteList.add(oldAttachment);
            }
        }

        // 找出新增的附件：现在有，数据库中没有
        for (String newAttachment : newAttachmentNames) {
            if (!oldAttachmentNames.contains(newAttachment)) {
                addList.add(newAttachment);
            }
        }

        // 将结果放入Map
        result.put("addList", addList);
        result.put("deleteList", deleteList);

        return result;
    }

    private Map<String, List<String>> compareBanks(CounterpartyInfoEntity entity, UpdateCounterpartyReqDTO reqDTO) {
        ArrayList<String> addList = new ArrayList<>();
        ArrayList<String> deleteList = new ArrayList<>();
        HashMap<String, List<String>> result = new HashMap<>();

        // 获取数据库中的银行账号
        List<String> oldAccountNumbers = new ArrayList<>();
        if (StringUtils.hasText(entity.getBankInfoIds())) {
            List<Integer> oldBankIds = Arrays.stream(entity.getBankInfoIds().split(","))
                    .map(Integer::parseInt)
                    .toList();
            oldAccountNumbers = bankInfoMapper.selectByIds(oldBankIds)
                    .stream()
                    .map(BankInfoEntity::getAccountNumber)
                    .toList();
        }

        // 获取新的银行账号
        List<String> newAccountNumbers = new ArrayList<>();
        if (reqDTO.getBankInfoList() != null && !reqDTO.getBankInfoList().isEmpty()) {
            newAccountNumbers = reqDTO.getBankInfoList()
                    .stream()
                    .map(BankInfoDTO::getAccountNumber)
                    .filter(Objects::nonNull)
                    .toList();
        }

        // 找出删除的账号：数据库中有，现在没有
        for (String oldAccount : oldAccountNumbers) {
            if (!newAccountNumbers.contains(oldAccount)) {
                deleteList.add(oldAccount);
            }
        }

        // 找出新增的账号：现在有，数据库中没有
        for (String newAccount : newAccountNumbers) {
            if (!oldAccountNumbers.contains(newAccount)) {
                addList.add(newAccount);
            }
        }

        // 将结果放入Map
        result.put("addList", addList);
        result.put("deleteList", deleteList);

        return result;
    }

    private Map<String, Map<String, String>> compareFieldsByDto(CounterpartyInfoEntity entity, UpdateCounterpartyReqDTO reqDTO) {
        Map<String, Map<String, String>> fieldChanges = new HashMap<>();

        // 比较交易方名称
        if (!Objects.equals(entity.getCounterpartyName(), reqDTO.getCounterpartyName())) {
            Map<String, String> change = new HashMap<>();
            change.put("oldValue", entity.getCounterpartyName());
            change.put("newValue", reqDTO.getCounterpartyName());
            fieldChanges.put("counterpartyName", change);
        }

        // 比较注册国家
        if (!Objects.equals(entity.getRegisterCountry(), reqDTO.getRegisterCountry())) {
            Map<String, String> change = new HashMap<>();
            change.put("oldValue", convertCountryCodeToName(entity.getRegisterCountry()));
            change.put("newValue", convertCountryCodeToName(reqDTO.getRegisterCountry()));
            fieldChanges.put("registerCountry", change);
        }

        // 比较交易方性质
        if (!Objects.equals(entity.getCounterpartyNature(), reqDTO.getCounterpartyNature())) {
            Map<String, String> change = new HashMap<>();
            change.put("oldValue", convertCounterpartyNatureToDesc(entity.getCounterpartyNature()));
            change.put("newValue", convertCounterpartyNatureToDesc(reqDTO.getCounterpartyNature()));
            fieldChanges.put("counterpartyNature", change);
        }

        // 比较交易方类型
        String oldTypeDesc = null;
        String newTypeDesc = null;

        if (entity.getCounterpartyType() != null) {
            List<String> oldTypeDescs = Arrays.stream(entity.getCounterpartyType().split(","))
                    .map(Integer::parseInt)
                    .map(this::convertCounterpartyTypeToDesc)
                    .filter(Objects::nonNull)
                    .toList();
            oldTypeDesc = String.join(",", oldTypeDescs);
        }

        if (reqDTO.getCounterpartyType() != null) {
            List<String> newTypeDescs = reqDTO.getCounterpartyType().stream()
                    .map(this::convertCounterpartyTypeToDesc)
                    .filter(Objects::nonNull)
                    .toList();
            newTypeDesc = String.join(",", newTypeDescs);
        }

        if (!Objects.equals(oldTypeDesc, newTypeDesc)) {
            Map<String, String> change = new HashMap<>();
            change.put("oldValue", oldTypeDesc);
            change.put("newValue", newTypeDesc);
            fieldChanges.put("counterpartyType", change);
        }

        // 比较是否关联方
        if (!Objects.equals(entity.getIsRelatedParty(), reqDTO.getIsRelatedParty())) {
            Map<String, String> change = new HashMap<>();
            change.put("oldValue", entity.getIsRelatedParty() != null ? (entity.getIsRelatedParty() ? "是" : "否") : null);
            change.put("newValue", reqDTO.getIsRelatedParty() != null ? (reqDTO.getIsRelatedParty() ? "是" : "否") : null);
            fieldChanges.put("isRelatedParty", change);
        }

        // 比较证件类型
        if (!Objects.equals(entity.getCertificateType(), reqDTO.getCertificateType())) {
            Map<String, String> change = new HashMap<>();
            change.put("oldValue", convertCertificateTypeToDesc(entity.getCertificateType()));
            change.put("newValue", convertCertificateTypeToDesc(reqDTO.getCertificateType()));
            fieldChanges.put("certificateType", change);
        }

        // 比较证件ID
        if (!Objects.equals(entity.getCertificateId(), reqDTO.getCertificateId())) {
            Map<String, String> change = new HashMap<>();
            change.put("oldValue", entity.getCertificateId());
            change.put("newValue", reqDTO.getCertificateId());
            fieldChanges.put("certificateId", change);
        }

        return fieldChanges;
    }

    private Integer getCurrentMaxVersion(long counterpartyId) {
        ChangeRecord changeRecord = changeRecordService.getOne(Wrappers.<ChangeRecord>lambdaQuery()
                .eq(ChangeRecord::getCounterpartyId, counterpartyId)
                .orderByDesc(ChangeRecord::getVersion)
                .last("LIMIT 1"));
        return changeRecord != null ? changeRecord.getVersion() : 0;
    }

    /**
     * 判断客户端类型
     *
     * @param request HTTP请求对象
     * @return 客户端类型：MOBILE（手机端）、WEB（网页端）、UNKNOWN（未知）
     */
    private String determineClientType(HttpServletRequest request) {
        String userAgent = request.getHeader("User-Agent");
        if (userAgent != null) {
            userAgent = userAgent.toLowerCase();
            // 检查是否包含移动设备标识
            if (userAgent.contains("mobile") ||
                    userAgent.contains("android") ||
                    userAgent.contains("iphone") ||
                    userAgent.contains("ipad") ||
                    userAgent.contains("blackberry") ||
                    userAgent.contains("windows phone")) {
                return "mobile";
            }
            // 检查是否为桌面浏览器
            if (userAgent.contains("mozilla") ||
                    userAgent.contains("chrome") ||
                    userAgent.contains("safari") ||
                    userAgent.contains("firefox") ||
                    userAgent.contains("edge")) {
                return "web";
            }
        }

        return "UNKNOWN";
    }

    /**
     * 预分配版本号，确保同一个交易方的所有操作使用相同版本
     */
    private Map<Long, Integer> preAllocateVersions(List<UploadCounterpartyDTO> counterpartyDataList, List<UploadCounterpartyBankDTO> bankDataList) {
        Map<Long, Integer> counterpartyVersionMap = new HashMap<>();
        Set<Long> affectedCounterpartyIds = new HashSet<>();

        // 收集所有需要修改的交易方ID
        for (UploadCounterpartyDTO dto : counterpartyDataList) {
            if (dto.getType() == ChangeTypeEnum.UPDATE) {
                CounterpartyInfoEntity existingEntity = findExistingCounterparty(dto);
                if (existingEntity != null) {
                    affectedCounterpartyIds.add(existingEntity.getId().longValue());
                }
            }
        }

        // 收集银行账户相关的交易方ID
        for (UploadCounterpartyBankDTO dto : bankDataList) {
            CounterpartyInfoEntity counterparty = findCounterpartyByCodeOrSerial(dto.getCounterpartyCode(), dto.getSerialNumber(), new HashMap<>());
            if (counterparty != null) {
                affectedCounterpartyIds.add(counterparty.getId().longValue());
            }
        }

        // 为每个受影响的交易方预分配版本号
        for (Long counterpartyId : affectedCounterpartyIds) {
            Integer currentMaxVersion = getCurrentMaxVersion(counterpartyId);
            Integer newVersion = (currentMaxVersion != null ? currentMaxVersion : 0) + 1;
            counterpartyVersionMap.put(counterpartyId, newVersion);
            log.info("为交易方ID{}预分配版本号：{}", counterpartyId, newVersion);
        }

        return counterpartyVersionMap;
    }

    /**
     * 处理交易方数据
     */
    private void processCounterpartyData(Map<ChangeTypeEnum, List<UploadCounterpartyDTO>> counterpartyMap, UserInfo currentUser, Map<Long, Integer> serialToCounterpartyIdMap, Map<Long, Integer> counterpartyVersionMap) {
        // 处理新增的交易方
        List<UploadCounterpartyDTO> createList = counterpartyMap.get(ChangeTypeEnum.CREATE);
        if (createList != null && !createList.isEmpty()) {
            log.info("开始处理新增交易方数据，共{}条", createList.size());
            for (UploadCounterpartyDTO dto : createList) {
                Integer counterpartyId = createCounterpartyFromUpload(dto, currentUser);
                // 记录序号到交易方ID的映射
                if (dto.getSerialNumber() != null && counterpartyId != null) {
                    serialToCounterpartyIdMap.put(dto.getSerialNumber(), counterpartyId);
                    log.info("记录序号映射：序号{}对应交易方ID{}", dto.getSerialNumber(), counterpartyId);
                }
            }
        }

        // 处理修改的交易方
        List<UploadCounterpartyDTO> updateList = counterpartyMap.get(ChangeTypeEnum.UPDATE);
        if (updateList != null && !updateList.isEmpty()) {
            log.info("开始处理修改交易方数据，共{}条", updateList.size());
            for (UploadCounterpartyDTO dto : updateList) {
                updateCounterpartyFromUpload(dto, currentUser, counterpartyVersionMap);
            }
        }
    }

    /**
     * 处理银行数据
     */
    private void processBankData(Map<ChangeTypeEnum, List<UploadCounterpartyBankDTO>> bankMap, UserInfo currentUser, Map<Long, Integer> serialToCounterpartyIdMap, Map<Long, Integer> counterpartyVersionMap) {
        // 处理新增的银行账户
        List<UploadCounterpartyBankDTO> createList = bankMap.get(ChangeTypeEnum.CREATE);
        if (createList != null && !createList.isEmpty()) {
            log.info("开始处理新增银行账户数据，共{}条", createList.size());
            for (UploadCounterpartyBankDTO dto : createList) {
                createBankFromUpload(dto, currentUser, serialToCounterpartyIdMap, counterpartyVersionMap);
            }
        }

        // 处理修改的银行账户
        List<UploadCounterpartyBankDTO> updateList = bankMap.get(ChangeTypeEnum.UPDATE);
        if (updateList != null && !updateList.isEmpty()) {
            log.info("开始处理修改银行账户数据，共{}条", updateList.size());
            for (UploadCounterpartyBankDTO dto : updateList) {
                updateBankFromUpload(dto, currentUser, counterpartyVersionMap);
            }
        }
    }

    /**
     * 从上传数据创建交易方
     */
    private Integer createCounterpartyFromUpload(UploadCounterpartyDTO dto, UserInfo currentUser) {
        try {
            CounterpartyInfoEntity entity = new CounterpartyInfoEntity();

            // 设置基本信息
            entity.setCounterpartyCode(StringUtils.hasText(dto.getCounterpartyCode()) ?
                    dto.getCounterpartyCode() : generateCounterpartyCode());
            entity.setCounterpartyName(dto.getCounterpartyName());
            entity.setRegisterCountry(dto.getRegisterCountry());
            entity.setCounterpartyNature(dto.getCounterpartyNature());
            entity.setCounterpartyType(dto.getCounterpartyType());
            entity.setIsRelatedParty(dto.getIsRelatedParty() != null ? dto.getIsRelatedParty() : false);
            entity.setCertificateType(dto.getCertificateType());
            entity.setCertificateId(dto.getCertificateId());

            // 设置审计字段
            entity.setIsEnabled(true);
            entity.setCreateAppid("upload");
            entity.setCreateSource("upload");
            entity.setUpdateAppid("upload");
            entity.setUpdateSource("upload");
            entity.setCreateId(Long.parseLong(currentUser.getUserId()));
            entity.setCreateBy(currentUser.getNickName());
            entity.setCreatedAt(LocalDateTime.now());
            entity.setUpdateBy(currentUser.getNickName());
            entity.setUpdatedAt(LocalDateTime.now());

            // 保存到数据库
            int result = counterpartyInfoMapper.insert(entity);
            if (result > 0) {
                log.info("成功创建交易方：{} - {}", entity.getCounterpartyCode(), entity.getCounterpartyName());
                return entity.getId();
            } else {
                log.error("创建交易方失败：{} - {}", dto.getCounterpartyCode(), dto.getCounterpartyName());
                throw new RuntimeException("创建交易方失败：" + dto.getCounterpartyName());
            }
        } catch (Exception e) {
            log.error("创建交易方失败：{} - {}, 错误信息：{}", dto.getCounterpartyCode(), dto.getCounterpartyName(), e.getMessage(), e);
            throw new RuntimeException("创建交易方失败：" + dto.getCounterpartyName(), e);
        }
    }

    /**
     * 从上传数据更新交易方
     */
    private void updateCounterpartyFromUpload(UploadCounterpartyDTO dto, UserInfo currentUser, Map<Long, Integer> counterpartyVersionMap) {
        try {
            // 根据交易方编码或序号查找现有交易方
            CounterpartyInfoEntity existingEntity = findExistingCounterparty(dto);
            if (existingEntity == null) {
                log.warn("未找到要更新的交易方：编码={}, 序号={}", dto.getCounterpartyCode(), dto.getSerialNumber());
                return;
            }

            // 记录变更前的数据，用于变更记录
            CounterpartyInfoEntity oldEntity = new CounterpartyInfoEntity();
            BeanUtils.copyProperties(existingEntity, oldEntity);

            // 更新字段
            if (StringUtils.hasText(dto.getCounterpartyName())) {
                existingEntity.setCounterpartyName(dto.getCounterpartyName());
            }
            if (StringUtils.hasText(dto.getRegisterCountry())) {
                existingEntity.setRegisterCountry(dto.getRegisterCountry());
            }
            if (dto.getCounterpartyNature() != null) {
                existingEntity.setCounterpartyNature(dto.getCounterpartyNature());
            }
            if (StringUtils.hasText(dto.getCounterpartyType())) {
                existingEntity.setCounterpartyType(dto.getCounterpartyType());
            }
            if (dto.getIsRelatedParty() != null) {
                existingEntity.setIsRelatedParty(dto.getIsRelatedParty());
            }
            if (dto.getCertificateType() != null) {
                existingEntity.setCertificateType(dto.getCertificateType());
            }
            if (StringUtils.hasText(dto.getCertificateId())) {
                existingEntity.setCertificateId(dto.getCertificateId());
            }

            // 更新审计字段
            existingEntity.setUpdateAppid("upload");
            existingEntity.setUpdateSource("upload");
            existingEntity.setUpdateBy(currentUser.getNickName());
            existingEntity.setUpdatedAt(LocalDateTime.now());

            // 保存变更记录（使用预分配的版本号）
            Integer preAllocatedVersion = counterpartyVersionMap.get(existingEntity.getId().longValue());
            if (preAllocatedVersion != null) {
                saveChangeRecordForUploadWithVersion(oldEntity, existingEntity, Long.parseLong(currentUser.getUserId()), currentUser.getNickName(), preAllocatedVersion);
            } else {
                saveChangeRecordForUpload(oldEntity, existingEntity, Long.parseLong(currentUser.getUserId()), currentUser.getNickName());
            }

            // 更新到数据库
            int result = counterpartyInfoMapper.updateById(existingEntity);
            if (result > 0) {
                log.info("成功更新交易方：{} - {}", existingEntity.getCounterpartyCode(), existingEntity.getCounterpartyName());
            } else {
                log.error("更新交易方失败：{} - {}", dto.getCounterpartyCode(), dto.getCounterpartyName());
            }
        } catch (Exception e) {
            log.error("更新交易方失败：{} - {}, 错误信息：{}", dto.getCounterpartyCode(), dto.getCounterpartyName(), e.getMessage(), e);
            throw new RuntimeException("更新交易方失败：" + dto.getCounterpartyName(), e);
        }
    }

    /**
     * 从上传数据创建银行账户
     */
    private void createBankFromUpload(UploadCounterpartyBankDTO dto, UserInfo currentUser, Map<Long, Integer> serialToCounterpartyIdMap, Map<Long, Integer> counterpartyVersionMap) {
        try {
            // 根据交易方编码或序号查找交易方
            CounterpartyInfoEntity counterparty = findCounterpartyByCodeOrSerial(dto.getCounterpartyCode(), dto.getSerialNumber(), serialToCounterpartyIdMap);
            if (counterparty == null) {
                log.warn("未找到对应交易方，无法创建银行账户：编码={}, 序号={}", dto.getCounterpartyCode(), dto.getSerialNumber());
                return;
            }

            // 创建银行信息请求DTO
            CreateBankInfoReqDTO bankReqDTO = new CreateBankInfoReqDTO();
            bankReqDTO.setBankCode("银行账户" + System.currentTimeMillis());
            bankReqDTO.setBankCountry(dto.getBankCountry());
            bankReqDTO.setBankName(dto.getBankName());
            bankReqDTO.setAccountName(dto.getAccountName());
            bankReqDTO.setAccountNumber(dto.getAccountNumber());
            bankReqDTO.setBankInternalId(dto.getBankInternalId());
            bankReqDTO.setBindingType("交易方");
            bankReqDTO.setBindingId(counterparty.getId().toString());

            // 创建银行信息
            CreateBankInfoRespDTO respDTO = bankInfoService.createBankInfo(bankReqDTO);
            if (respDTO != null && respDTO.getId() != null) {
                log.info("成功为交易方{}创建银行账户：{}", counterparty.getCounterpartyName(), dto.getAccountNumber());

                // 记录银行账户新增的变更记录（使用预分配版本号）
                Integer preAllocatedVersion = counterpartyVersionMap.get(counterparty.getId().longValue());
                if (preAllocatedVersion != null) {
                    saveBankChangeRecordWithVersion(counterparty.getId().longValue(), "bank", null, dto.getAccountNumber(),
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName(), preAllocatedVersion);
                }
            } else {
                log.error("为交易方{}创建银行账户失败：{}", counterparty.getCounterpartyName(), dto.getAccountNumber());
            }
        } catch (Exception e) {
            log.error("创建银行账户失败：{}, 错误信息：{}", dto.getAccountNumber(), e.getMessage(), e);
            throw new RuntimeException("创建银行账户失败：" + dto.getAccountNumber(), e);
        }
    }

    /**
     * 从上传数据更新银行账户（删除原来的，新增新的）
     */
    private void updateBankFromUpload(UploadCounterpartyBankDTO dto, UserInfo currentUser, Map<Long, Integer> counterpartyVersionMap) {
        try {
            if (dto.getId() == null) {
                log.warn("银行账户ID为空，无法更新：{}", dto.getAccountNumber());
                return;
            }

            // 查找现有银行信息
            BankInfoEntity existingBank = bankInfoMapper.selectById(dto.getId());
            if (existingBank == null) {
                log.warn("未找到要更新的银行账户：ID={}", dto.getId());
                return;
            }

            // 根据交易方编码或序号查找交易方（修改银行账户时，序号映射表为空，直接使用现有逻辑）
            CounterpartyInfoEntity counterparty = findCounterpartyByCodeOrSerial(dto.getCounterpartyCode(), dto.getSerialNumber(), new HashMap<>());
            if (counterparty == null) {
                log.warn("未找到对应交易方，无法更新银行账户：编码={}, 序号={}", dto.getCounterpartyCode(), dto.getSerialNumber());
                return;
            }

            // 记录原账号（用于变更记录）
            String oldAccountNumber = existingBank.getAccountNumber();

            // 删除原有的银行账户记录
            int deleteResult = bankInfoMapper.deleteById(dto.getId());
            if (deleteResult > 0) {
                log.info("成功删除原银行账户：{}", oldAccountNumber);

                // 记录删除操作的变更记录（使用预分配版本号）
                Integer preAllocatedVersion = counterpartyVersionMap.get(counterparty.getId().longValue());
                if (preAllocatedVersion != null) {
                    saveBankChangeRecordWithVersion(counterparty.getId().longValue(), "bank", oldAccountNumber, null,
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName(), preAllocatedVersion);
                } else {
                    saveBankChangeRecord(counterparty.getId().longValue(), "bank", oldAccountNumber, null,
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName());
                }
            }

            // 创建新的银行账户
            CreateBankInfoReqDTO bankReqDTO = new CreateBankInfoReqDTO();
            bankReqDTO.setBankCode("银行账户" + System.currentTimeMillis());
            bankReqDTO.setBankCountry(dto.getBankCountry());
            bankReqDTO.setBankName(dto.getBankName());
            bankReqDTO.setAccountName(dto.getAccountName());
            bankReqDTO.setAccountNumber(dto.getAccountNumber());
            bankReqDTO.setBankInternalId(dto.getBankInternalId());
            bankReqDTO.setBindingType("交易方");
            bankReqDTO.setBindingId(counterparty.getId().toString());

            // 创建新银行信息
            CreateBankInfoRespDTO respDTO = bankInfoService.createBankInfo(bankReqDTO);
            if (respDTO != null && respDTO.getId() != null) {
                log.info("成功为交易方{}创建新银行账户：{}", counterparty.getCounterpartyName(), dto.getAccountNumber());

                // 记录新增操作的变更记录（使用预分配版本号）
                Integer preAllocatedVersion = counterpartyVersionMap.get(counterparty.getId().longValue());
                if (preAllocatedVersion != null) {
                    saveBankChangeRecordWithVersion(counterparty.getId().longValue(), "bank", null, dto.getAccountNumber(),
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName(), preAllocatedVersion);
                } else {
                    saveBankChangeRecord(counterparty.getId().longValue(), "bank", null, dto.getAccountNumber(),
                            Long.parseLong(currentUser.getUserId()), currentUser.getNickName());
                }
            } else {
                log.error("为交易方{}创建新银行账户失败：{}", counterparty.getCounterpartyName(), dto.getAccountNumber());
                throw new RuntimeException("创建新银行账户失败：" + dto.getAccountNumber());
            }

        } catch (Exception e) {
            log.error("更新银行账户失败：{}, 错误信息：{}", dto.getAccountNumber(), e.getMessage(), e);
            throw new RuntimeException("更新银行账户失败：" + dto.getAccountNumber(), e);
        }
    }

    /**
     * 查找现有交易方（按编码或序号）
     */
    private CounterpartyInfoEntity findExistingCounterparty(UploadCounterpartyDTO dto) {
        if (StringUtils.hasText(dto.getCounterpartyCode())) {
            // 按交易方编码查找
            LambdaQueryWrapper<CounterpartyInfoEntity> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(CounterpartyInfoEntity::getCounterpartyCode, dto.getCounterpartyCode());
            return counterpartyInfoMapper.selectOne(wrapper);
        } else if (dto.getSerialNumber() != null) {
            // 按序号查找（这里假设序号就是ID，根据实际情况调整）
            return counterpartyInfoMapper.selectById(dto.getSerialNumber().intValue());
        }
        return null;
    }

    /**
     * 根据编码或序号查找交易方
     */
    private CounterpartyInfoEntity findCounterpartyByCodeOrSerial(String counterpartyCode, Long serialNumber, Map<Long, Integer> serialToCounterpartyIdMap) {
        if (StringUtils.hasText(counterpartyCode)) {
            LambdaQueryWrapper<CounterpartyInfoEntity> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(CounterpartyInfoEntity::getCounterpartyCode, counterpartyCode);
            return counterpartyInfoMapper.selectOne(wrapper);
        } else if (serialNumber != null) {
            // 先从映射表中查找新创建的交易方
            Integer counterpartyId = serialToCounterpartyIdMap.get(serialNumber);
            if (counterpartyId != null) {
                return counterpartyInfoMapper.selectById(counterpartyId);
            }
            // 如果映射表中没有，再从数据库中按ID查找（兼容旧数据）
            return counterpartyInfoMapper.selectById(serialNumber.intValue());
        }
        return null;
    }

    /**
     * 为上传操作保存变更记录（使用预分配版本号）
     */
    private void saveChangeRecordForUploadWithVersion(CounterpartyInfoEntity oldEntity, CounterpartyInfoEntity newEntity, Long userId, String username, Integer version) {
        try {
            List<ChangeRecord> changeRecords = new ArrayList<>();

            // 比较各个字段并记录变更
            if (!Objects.equals(oldEntity.getCounterpartyName(), newEntity.getCounterpartyName())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "counterpartyName",
                        oldEntity.getCounterpartyName(), newEntity.getCounterpartyName(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getRegisterCountry(), newEntity.getRegisterCountry())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "registerCountry",
                        oldEntity.getRegisterCountry(), newEntity.getRegisterCountry(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCounterpartyNature(), newEntity.getCounterpartyNature())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "counterpartyNature",
                        oldEntity.getCounterpartyNature() != null ? oldEntity.getCounterpartyNature().toString() : null,
                        newEntity.getCounterpartyNature() != null ? newEntity.getCounterpartyNature().toString() : null,
                        userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCounterpartyType(), newEntity.getCounterpartyType())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "counterpartyType",
                        oldEntity.getCounterpartyType(), newEntity.getCounterpartyType(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getIsRelatedParty(), newEntity.getIsRelatedParty())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "isRelatedParty",
                        oldEntity.getIsRelatedParty() != null ? oldEntity.getIsRelatedParty().toString() : null,
                        newEntity.getIsRelatedParty() != null ? newEntity.getIsRelatedParty().toString() : null,
                        userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCertificateType(), newEntity.getCertificateType())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "certificateType",
                        oldEntity.getCertificateType() != null ? oldEntity.getCertificateType().toString() : null,
                        newEntity.getCertificateType() != null ? newEntity.getCertificateType().toString() : null,
                        userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCertificateId(), newEntity.getCertificateId())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), version, "certificateId",
                        oldEntity.getCertificateId(), newEntity.getCertificateId(), userId, username);
                changeRecords.add(record);
            }

            // 批量保存变更记录
            if (!changeRecords.isEmpty()) {
                changeRecordService.saveBatch(changeRecords);
                log.info("保存上传操作变更记录成功，交易方ID: {}, 预分配版本号: {}, 变更记录数: {}",
                        newEntity.getId(), version, changeRecords.size());
            }
        } catch (Exception e) {
            log.error("保存上传操作变更记录失败：{}", e.getMessage(), e);
        }
    }

    /**
     * 为上传操作保存变更记录
     */
    private void saveChangeRecordForUpload(CounterpartyInfoEntity oldEntity, CounterpartyInfoEntity newEntity, Long userId, String username) {
        try {
            // 获取当前最大版本号
            Integer currentMaxVersion = getCurrentMaxVersion(newEntity.getId().longValue());
            Integer newVersion = (currentMaxVersion != null ? currentMaxVersion : 0) + 1;

            List<ChangeRecord> changeRecords = new ArrayList<>();

            // 比较各个字段并记录变更
            if (!Objects.equals(oldEntity.getCounterpartyName(), newEntity.getCounterpartyName())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "counterpartyName",
                        oldEntity.getCounterpartyName(), newEntity.getCounterpartyName(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getRegisterCountry(), newEntity.getRegisterCountry())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "registerCountry",
                        oldEntity.getRegisterCountry(), newEntity.getRegisterCountry(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCounterpartyNature(), newEntity.getCounterpartyNature())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "counterpartyNature",
                        oldEntity.getCounterpartyNature() != null ? oldEntity.getCounterpartyNature().toString() : null,
                        newEntity.getCounterpartyNature() != null ? newEntity.getCounterpartyNature().toString() : null,
                        userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCounterpartyType(), newEntity.getCounterpartyType())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "counterpartyType",
                        oldEntity.getCounterpartyType(), newEntity.getCounterpartyType(), userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getIsRelatedParty(), newEntity.getIsRelatedParty())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "isRelatedParty",
                        oldEntity.getIsRelatedParty() != null ? oldEntity.getIsRelatedParty().toString() : null,
                        newEntity.getIsRelatedParty() != null ? newEntity.getIsRelatedParty().toString() : null,
                        userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCertificateType(), newEntity.getCertificateType())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "certificateType",
                        oldEntity.getCertificateType() != null ? oldEntity.getCertificateType().toString() : null,
                        newEntity.getCertificateType() != null ? newEntity.getCertificateType().toString() : null,
                        userId, username);
                changeRecords.add(record);
            }

            if (!Objects.equals(oldEntity.getCertificateId(), newEntity.getCertificateId())) {
                ChangeRecord record = createChangeRecord(newEntity.getId().longValue(), newVersion, "certificateId",
                        oldEntity.getCertificateId(), newEntity.getCertificateId(), userId, username);
                changeRecords.add(record);
            }

            // 批量保存变更记录
            if (!changeRecords.isEmpty()) {
                changeRecordService.saveBatch(changeRecords);
                log.info("保存上传操作变更记录成功，交易方ID: {}, 版本号: {}, 变更记录数: {}",
                        newEntity.getId(), newVersion, changeRecords.size());
            }
        } catch (Exception e) {
            log.error("保存上传操作变更记录失败：{}", e.getMessage(), e);
        }
    }

    /**
     * 创建变更记录
     */
    private ChangeRecord createChangeRecord(Long counterpartyId, Integer version, String field,
                                            String oldValue, String newValue, Long userId, String username) {
        ChangeRecord changeRecord = new ChangeRecord();
        changeRecord.setCounterpartyId(counterpartyId);
        changeRecord.setVersion(version);
        changeRecord.setField(field);
        changeRecord.setOldValue(oldValue);
        changeRecord.setNewValue(newValue);
        changeRecord.setCreateId(userId);
        changeRecord.setCreateBy(username);
        changeRecord.setCreateTime(new Date());
        return changeRecord;
    }

    /**
     * 保存银行账户变更记录（使用预分配版本号）
     */
    private void saveBankChangeRecordWithVersion(Long counterpartyId, String field, String oldValue, String newValue, Long userId, String username, Integer version) {
        try {
            // 创建变更记录
            ChangeRecord changeRecord = new ChangeRecord();
            changeRecord.setCounterpartyId(counterpartyId);
            changeRecord.setVersion(version);
            changeRecord.setField(field);
            changeRecord.setOldValue(oldValue);
            changeRecord.setNewValue(newValue);
            changeRecord.setCreateId(userId);
            changeRecord.setCreateBy(username);
            changeRecord.setCreateTime(new Date());

            // 保存变更记录
            changeRecordService.save(changeRecord);

            log.info("保存银行账户变更记录成功，交易方ID: {}, 预分配版本号: {}, 字段: {}, 旧值: {}, 新值: {}",
                    counterpartyId, version, field, oldValue, newValue);
        } catch (Exception e) {
            log.error("保存银行账户变更记录失败：{}", e.getMessage(), e);
        }
    }

    /**
     * 保存银行账户变更记录
     */
    private void saveBankChangeRecord(Long counterpartyId, String field, String oldValue, String newValue, Long userId, String username) {
        try {
            // 获取当前最大版本号
            Integer currentMaxVersion = getCurrentMaxVersion(counterpartyId);
            Integer newVersion = (currentMaxVersion != null ? currentMaxVersion : 0) + 1;

            // 创建变更记录
            ChangeRecord changeRecord = new ChangeRecord();
            changeRecord.setCounterpartyId(counterpartyId);
            changeRecord.setVersion(newVersion);
            changeRecord.setField(field);
            changeRecord.setOldValue(oldValue);
            changeRecord.setNewValue(newValue);
            changeRecord.setCreateId(userId);
            changeRecord.setCreateBy(username);
            changeRecord.setCreateTime(new Date());

            // 保存变更记录
            changeRecordService.save(changeRecord);

            log.info("保存银行账户变更记录成功，交易方ID: {}, 版本号: {}, 字段: {}, 旧值: {}, 新值: {}",
                    counterpartyId, newVersion, field, oldValue, newValue);
        } catch (Exception e) {
            log.error("保存银行账户变更记录失败：{}", e.getMessage(), e);
        }
    }

    private void validateCounterpartyHeaders(XSSFSheet sheet) {
        Row headerRow = sheet.getRow(0); // 假设第2行是表头
        if (headerRow == null) {
            throw new RuntimeException("交易方信息表缺少表头行");
        }

        String[] expectedHeaders = {"变更类型", "基础信息序号", "交易方编码", "交易方名称", "注册国家/地区", "交易方性质", "交易方类型", "是否关联方", "证件类型", "证件ID"};
        for (int i = 0; i < expectedHeaders.length; i++) {
            Cell cell = headerRow.getCell(i);
            if (cell == null || !expectedHeaders[i].equals(cell.getStringCellValue())) {
                throw new RuntimeException("交易方信息表第" + (i + 1) + "列表头不正确，期望：" + expectedHeaders[i]);
            }
        }
    }

    private void validateBankHeaders(XSSFSheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            throw new RuntimeException("银行信息表缺少表头行");
        }

        String[] expectedHeaders = {"变更类型", "账户信息 ID", "基础信息序号", "交易方编码", "账号", "账户名", "银行内部ID", "银行名称", "银行国家"};
        for (int i = 0; i < expectedHeaders.length; i++) {
            Cell cell = headerRow.getCell(i);
            if (cell == null || !expectedHeaders[i].equals(cell.getStringCellValue())) {
                throw new RuntimeException("银行信息表第" + (i + 1) + "列表头不正确，期望：" + expectedHeaders[i]);
            }
        }
    }


}
